package dao;

import dto.TarifaDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author lucifer
 */
public class TarifaDAO {

  public static final String Tab =
    "create table tarifa (" +
    "id serial," + //pk
    "id_obra_social int," + //fk
    "id_especialidad int," + //fk
    "precio decimal(12,2)" +  
    ");"
  ;

  public static final String Tuples = "";
  /*
    "insert into tarifa (id_obra_social, id_especialidad, precio) values" +
    "(0, 1,  50.25)," +
    "(0, 2,  70.50)," +
    "(0, 3,  64.99)," +
    "(0, 4,  60.40)," +
    "(0, 5,  85.05)," +
    "(0, 6,  45.15)," +
    "(0, 7, 125.00)," +

    "(1, 1,  71.99)," +
    "(1, 2,  75.00)," +
    "(1, 3,  70.25)," +
    "(1, 4,  69.99)," +
    "(1, 5,  87.50)," +
    "(1, 6,  63.75)," +
    "(1, 7, 150.00)," +

    "(2, 1,  81.59)," +
    "(2, 2,  88.50)," +
    "(2, 3,  73.05)," +
    "(2, 4,  70.00)," +
    "(2, 5,  95.99)," +
    "(2, 6,  65.75)," +
    "(2, 7, 155.99)," +

    "(3, 1,  83.95)," +
    "(3, 2,  99.99)," +
    "(3, 3,  95.50)," +
    "(3, 4,  84.25)," +
    "(3, 5, 105.95)," +
    "(3, 6,  75.75)," +
    "(3, 7, 165.00)," +

    "(4, 1, 151.99)," +
    "(4, 2, 173.00)," +
    "(4, 3, 164.25)," +
    "(4, 4, 160.99)," +
    "(4, 5, 185.50)," +
    "(4, 6, 145.75)," +
    "(4, 7, 225.00)," +

    "(5, 1, 171.59)," +
    "(5, 2, 175.50)," +
    "(5, 3, 173.05)," +
    "(5, 4, 169.00)," +
    "(5, 5, 187.99)," +
    "(5, 6, 163.75)," +
    "(5, 7, 255.99)," +

    "(6, 1, 183.95)," +
    "(6, 2, 199.99)," +
    "(6, 3, 195.50)," +
    "(6, 4, 184.25)," +
    "(6, 5, 205.95)," +
    "(6, 6, 175.75)," +
    "(6, 7, 265.00)," +

    "(7, 1, 283.00)," +
    "(7, 2, 273.99)," +
    "(7, 3, 264.95)," +
    "(7, 4, 284.25)," +
    "(7, 5, 274.50)," +
    "(7, 6, 263.75)," +
    "(7, 7, 315.00);"
  ;
  */

  private static TarifaDAO dao = null;
  private final Connection c;

  public static TarifaDAO createDAO() throws SQLException  {
    if (dao == null) {
      dao = new TarifaDAO();
    }
    return dao;
  }

  private TarifaDAO() throws SQLException {
    c = DB.PQ().Conn();
  }

  public int LastID() throws SQLException {
    return DB.PQ().LastIDFrom("tarifa");
  }

  //public TarifaDTO SearchByObraSocialIDEspecialidadID(int idObraSocial, int idEspecialidad) throws SQLException {
    //return getTarifaDTOs(queryByObraSocialIDEspecialidadID(idObraSocial, idEspecialidad)).get(0);
  //}

  public TarifaDTO SearchByObraSocialIDEspecialidadID(int idObraSocial, int idEspecialidad) throws SQLException {
    String query = String.format(
      "select * from tarifa where id_obra_social = %d and id_especialidad = %d;",
      idObraSocial, idEspecialidad
    );
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    TarifaDTO dto = null;
    if (r.next()) {
      dto = new TarifaDTO(
        r.getInt("id"),
        r.getInt("id_obra_social"),
        r.getInt("id_especialidad"),
        r.getDouble("precio")
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  public ArrayList<TarifaDTO> SearchByObraSocialID(int idObraSocial) throws SQLException {
    return getTarifaDTOs(queryByObraSocialID(idObraSocial));
  }

  public ArrayList<TarifaDTO> SearchByEspecialidadID(int idEspecialidad) throws SQLException {
    return getTarifaDTOs(queryByEspecialidadID(idEspecialidad));
  }

  private String queryByObraSocialIDEspecialidadID(int idObraSocial, int idEspecialidad) throws SQLException {
    String query = String.format(
      "select * from tarifa where id_obra_social = %d and id_especialidad = %d;",
      idObraSocial, idEspecialidad
    );
    return query;
  }

  private String queryByObraSocialID(int idObraSocial) throws SQLException {
    String query = String.format(
      "select * from tarifa where id_obra_social = %d;",
      idObraSocial
    );
    return query;
  }

  private String queryByEspecialidadID(int idEspecialidad) throws SQLException {
    String query = String.format(
      "select * from tarifa where id_espcialidad = %d;", idEspecialidad
    );
    return query;
  }

  private ArrayList<TarifaDTO> getTarifaDTOs(String query) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<TarifaDTO> tarifas = new ArrayList<>();
    while (r.next()) {
      tarifas.add(
        new TarifaDTO(
          r.getInt("id"),
          r.getInt("id_obra_social"),
          r.getInt("id_especialidad"),
          r.getDouble("precio")
        )
      );
    }
    r.close();
    s.close();
    return tarifas;
  }

  public TarifaDTO TarifaDTO(int id) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(
      "select * from tarifa where id = " + id
    );
    TarifaDTO dto = null;
    if (r.next()) {
      dto = new TarifaDTO(
        r.getInt("id"),
        r.getInt("id_obra_social"),
        r.getInt("id_especialidad"),
        r.getDouble("precio")
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  public int Save(TarifaDTO t) throws SQLException {
    String query = String.format(
      "insert into tarifa values (default, %d, %d, %s);",
      t.getIdObraSocial(), t.getIdEspecialidad(), t.getPrecio()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return LastID();
  }

  public void EditDouble(int id, String attr, double p) throws SQLException {
    String query = String.format(
      "update tarifa set %s = %s where id = %d;",
      attr, p, id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }

}
